from typing import Optional
from models.work import LiteraryWork
from models.author import Author  # 添加导入
from utils.database import get_db
from sqlalchemy.orm import Session
from sqlalchemy import text


# 修改 get_works 函数
def get_works(page: int = 1, page_size: int = 10) -> dict:
    """获取所有作品信息（分页）

    Args:
        page (int): 页码，默认为1
        page_size (int): 每页数量，默认为10

    Returns:
        dict: 包含作品列表和分页信息的字典
    """
    db: Session = next(get_db())
    try:
        # 计算偏移量
        offset = (page - 1) * page_size

        # 执行SQL查询获取所有作品及其作者信息（分页）
        result = db.execute(text("""SELECT w.id, w.title, w.genre, w.creation_year, 
               a.name as author_name, a.alias as author_alias, a.dynasty as author_dynasty
               FROM works w
               LEFT JOIN authors a ON w.author_id = a.id
               ORDER BY w.id
               LIMIT :limit OFFSET :offset"""),
                            {"limit": page_size, "offset": offset})
        # works = [LiteraryWork(id=row[0], title=row[1], original_title=row[2], content=row[3],
        #                       author_id=row[4], category_id=row[5], genre=row[6], dynasty=row[7],
        #                       creation_year=row[8], background=row[9], meaning=row[10], annotation=row[11],
        #                       cover_image=row[12], view_count=row[13], is_recommended=row[14],
        #                       created_at=row[15], updated_at=row[16],
        #                       author=Author(id=row[4], name=row[17], alias=row[18], dynasty=row[19]) if row[4] else None)
        #          for row in result.fetchall()]
        works = []
        for row in result.fetchall():
            works.append({
                "id": row[0],
                "title": row[1],
                "genre": row[2],
                "creation_year": row[3],
                "author": {
                    "name": row[4],
                    "alias": row[5],
                    "dynasty": row[6]
                }
            })

        # 获取总数量
        count_result = db.execute(text("SELECT COUNT(*) FROM works"))
        total = count_result.fetchone()[0]

        return {
            "data": works,
            "total": total,
            "page": page,
            "page_size": page_size
        }
    finally:
        db.close()


# 修改 get_works_by_category 函数
def get_works_by_category(category_id: int, page: int = 1, page_size: int = 10) -> dict:
    """根据分类ID获取作品信息（分页）

    Args:
        category_id (int): 分类ID
        page (int): 页码，默认为1
        page_size (int): 每页数量，默认为10

    Returns:
        dict: 包含作品列表和分页信息的字典
    """
    db: Session = next(get_db())
    try:
        # 计算偏移量
        offset = (page - 1) * page_size

        # 执行SQL查询获取指定分类下的所有作品及其作者信息（分页）
        result = db.execute(text("""SELECT w.id, w.title, w.original_title, w.content, w.author_id, w.category_id, 
               w.genre, w.dynasty, w.creation_year, w.background, w.meaning, w.annotation, w.cover_image, 
               w.view_count, w.is_recommended, w.created_at, w.updated_at,
               a.name as author_name, a.alias as author_alias, a.dynasty as author_dynasty
               FROM works w
               LEFT JOIN authors a ON w.author_id = a.id 
               WHERE w.category_id = :category_id
               ORDER BY w.id
               LIMIT :limit OFFSET :offset"""),
                            {"category_id": category_id, "limit": page_size, "offset": offset})
        works = [LiteraryWork(id=row[0], title=row[1], original_title=row[2], content=row[3],
                              author_id=row[4], category_id=row[5], genre=row[6], dynasty=row[7],
                              creation_year=row[8], background=row[9], meaning=row[10], annotation=row[11],
                              cover_image=row[12], view_count=row[13], is_recommended=row[14],
                              created_at=row[15], updated_at=row[16],
                              author=Author(id=row[4], name=row[17], alias=row[18], dynasty=row[19]) if row[4] else None)
                 for row in result.fetchall()]

        # 获取总数量
        count_result = db.execute(text("SELECT COUNT(*) FROM works WHERE category_id = :category_id"),
                                  {"category_id": category_id})
        total = count_result.fetchone()[0]

        return {
            "data": works,
            "total": total,
            "page": page,
            "page_size": page_size
        }
    finally:
        db.close()


def get_works_by_parent_category(parent_id: int, page: int = 1, page_size: int = 10) -> dict:
    """根据父分类ID获取其所有子分类下的作品（分页）

    Args:
        parent_id (int): 父分类ID
        page (int): 页码，默认为1
        page_size (int): 每页数量，默认为10

    Returns:
        dict: 包含作品列表和分页信息的字典
    """
    db: Session = next(get_db())
    try:
        # 计算偏移量
        offset = (page - 1) * page_size

        # 执行SQL查询获取指定父分类下所有子分类的作品及其作者信息（分页）
        result = db.execute(text("""SELECT w.id, w.title, w.original_title, w.content, w.author_id, w.category_id, 
               w.genre, w.dynasty, w.creation_year, w.background, w.meaning, w.annotation, w.cover_image, 
               w.view_count, w.is_recommended, w.created_at, w.updated_at,
               a.name as author_name, a.alias as author_alias, a.dynasty as author_dynasty
               FROM works w
               LEFT JOIN authors a ON w.author_id = a.id 
               WHERE w.category_id IN (SELECT id FROM categories WHERE parent_id = :parent_id)
               ORDER BY w.id
               LIMIT :limit OFFSET :offset"""),
                            {"parent_id": parent_id, "limit": page_size, "offset": offset})
        works = [LiteraryWork(id=row[0], title=row[1], original_title=row[2], content=row[3],
                              author_id=row[4], category_id=row[5], genre=row[6], dynasty=row[7],
                              creation_year=row[8], background=row[9], meaning=row[10], annotation=row[11],
                              cover_image=row[12], view_count=row[13], is_recommended=row[14],
                              created_at=row[15], updated_at=row[16],
                              author=Author(id=row[4], name=row[17], alias=row[18], dynasty=row[19]) if row[4] else None)
                 for row in result.fetchall()]

        # 获取总数量
        count_result = db.execute(text("SELECT COUNT(*) FROM works w WHERE w.category_id IN (SELECT id FROM categories WHERE parent_id = :parent_id)"),
                                  {"parent_id": parent_id})
        total = count_result.fetchone()[0]

        return {
            "data": works,
            "total": total,
            "page": page,
            "page_size": page_size
        }
    finally:
        db.close()


def get_work_by_id(work_id: int) -> Optional[LiteraryWork]:
    """根据作品ID获取作品信息

    Args:
        work_id (int): 作品ID

    Returns:
        Optional[LiteraryWork]: 如果找到对应ID的作品则返回该作品，否则返回None
    """
    db: Session = next(get_db())
    try:
        # 执行SQL查询获取指定ID的作品及其作者信息
        result = db.execute(text("""SELECT w.title, w.content, w.genre, w.dynasty, w.background, w.meaning, w.annotation,
               a.name as author_name, a.dynasty as author_dynasty, a.intro
               FROM works w
               LEFT JOIN authors a ON w.author_id = a.id 
               WHERE w.id = :work_id"""), {"work_id": work_id})
        row = result.fetchone()
        if row:
            worksInfo = {
                "title": row[0],
                "content": row[1],
                "genre": row[2],
                "dynasty": row[3],
                "background": row[4],
                "meaning": row[5],
                "annotation": row[6],
                "author": {
                    "name": row[7],
                    "dynasty": row[8],
                    "intro": row[9]
                }
            }
            return worksInfo
        else:
            return None
        # if row:
        #     return LiteraryWork(id=row[0], title=row[1], original_title=row[2], content=row[3],
        #                         author_id=row[4], category_id=row[5], genre=row[6], dynasty=row[7],
        #                         creation_year=row[8], background=row[9], meaning=row[10], annotation=row[11],
        #                       cover_image=row[12], view_count=row[13], is_recommended=row[14],
        #                       created_at=row[15], updated_at=row[16],
        #                       author=Author(id=row[4], name=row[17], alias=row[18], dynasty=row[19]) if row[4] else None)
        # return None
    finally:
        db.close()


# 修改 search_works 函数
def search_works(keyword: str, page: int = 1, page_size: int = 10) -> dict:
    """根据关键词搜索作品信息（标题或作者名）（分页）

    Args:
        keyword (str): 搜索关键词
        page (int): 页码，默认为1
        page_size (int): 每页数量，默认为10

    Returns:
        dict: 包含匹配作品信息和分页信息的字典
    """
    db: Session = next(get_db())
    try:
        # 计算偏移量
        offset = (page - 1) * page_size

        # 执行SQL查询搜索作品及其作者信息（分页）
        result = db.execute(text("""SELECT w.id, w.title, w.genre, w.creation_year, 
               a.name as author_name, a.alias as author_alias, a.dynasty as author_dynasty
               FROM works w
               LEFT JOIN authors a ON w.author_id = a.id
               WHERE w.title LIKE :keyword OR a.name LIKE :keyword
               ORDER BY w.id
               LIMIT :limit OFFSET :offset"""),
                            {"keyword": f"%{keyword}%", "limit": page_size, "offset": offset})
        # works = [LiteraryWork(id=row[0], title=row[1], original_title=row[2], content=row[3],
        #                       author_id=row[4], category_id=row[5], genre=row[6], dynasty=row[7],
        #                       creation_year=row[8], background=row[9], meaning=row[10], annotation=row[11],
        #                       cover_image=row[12], view_count=row[13], is_recommended=row[14],
        #                       created_at=row[15], updated_at=row[16],
        #                       author=Author(id=row[4], name=row[17], alias=row[18], dynasty=row[19]) if row[4] else None)
        #          for row in result.fetchall()]

        # 获取总数量
        count_result = db.execute(text("""SELECT COUNT(*) FROM works w
               LEFT JOIN authors a ON w.author_id = a.id 
               WHERE w.title LIKE :keyword OR a.name LIKE :keyword"""),
                                  {"keyword": f"%{keyword}%"})
        total = count_result.fetchone()[0]
        works = []
        for row in result.fetchall():
            works.append({
                "id": row[0],
                "title": row[1],
                "genre": row[2],
                "creation_year": row[3],
                "author": {
                    "name": row[4],
                    "alias": row[5],
                    "dynasty": row[6]
                }
            })

        return {
            "data": works,
            "total": total,
            "page": page,
            "page_size": page_size
        }
    finally:
        db.close()